package com.info.zhiduoduo.repository.mapper.crowdsource;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.crowdsource.*;
import com.info.zhiduoduo.repository.dto.project.SameProjectDto;
import com.info.zhiduoduo.repository.entity.crowdsource.CrowdsourceProjectItem;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 *
 *
 *
 * <p>
 * 智众包项目人员信息 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-09-25
 */
public interface CrowdsourceProjectItemMapper extends BaseMapper<CrowdsourceProjectItem> {

	@Select(" select cspi.*,(cspi.integrated_tax_fee - cspi.project_amount) as totalCharge," +
			" cstri.repay,cstri.status," +
			" IF(cspi.user_type=1,csa.idno,ma.idno) AS idno," +
			" IF(cspi.user_type=1,csa.acctno,ub.acctno) AS acctno," +
			" ub.alipay_account as alipayAccount," +
			" IF(cspi.user_type=1,csa.acctphone,ub.telephone) AS acctphone," +
			" IF(cspi.user_type=1,csa.bankname,ub.bankname) AS bankname, " +
			" cstri.tradetime,ci.finishtime,ci.invoice_picture " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid " +
			" left join zdd_crowdsource_trade_record_item cstri on cspi.id = cstri.project_item_id and cstri.repay = 0 " +
			" left join zdd_crowdsource_account csa on csa.crowdsourceid = cspi.userid and cspi.user_type = 1 " +
			" left join zdd_maker_account ma on ma.userid = cspi.userid and cspi.user_type = 2 " +
			" left join zdd_maker_bankcard ub on ub.userid = ma.userid and ub.state = 1 and ub.accttype = 1 " +
			" LEFT JOIN zdd_crowdsource_invoice ci ON ci.project_item_id = cspi.id " +
			" where cspi.projectid = #{projectid} " +
			" and cspi.deleted = 0 " +
			" order by cspi.id ")
	List<CrowdsourceProjectItemDto> getItemList(Integer projectid);

	@Select(" select cspi.*,(cspi.integrated_tax_fee - project_amount) as totalCharge," +
			" cstri.repay,cstri.status,csp.project_name,csp.project_desc, " +
			" IF(cspi.user_type=1,csa.idno,ma.idno) AS idno," +
			" IF(cspi.user_type=1,csa.acctno,ub.acctno) AS acctno," +
			" IF(cspi.user_type=1,csa.acctphone,ub.telephone) AS acctphone," +
			" IF(cspi.user_type=1,csa.bankname,ub.bankname) AS bankname," +
			" csp.bid_type,csp.bid_end,csp.bid_project_finish,csp.notify_upload_delivery " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid " +
			" left join zdd_crowdsource_trade_record_item cstri on cspi.id = cstri.project_item_id and cstri.repay = 0 " +
			" left join zdd_crowdsource_account csa on csa.crowdsourceid = cspi.userid and cspi.user_type = 1 " +
			" left join zdd_maker_account ma on ma.userid = cspi.userid and cspi.user_type = 2 " +
			" left join zdd_maker_bankcard ub on ub.userid = ma.userid and ub.state = 1 and ub.accttype = 1 " +
			" where cspi.id = #{id}")
	CrowdsourceProjectItemDto getItemDto(Integer id);

	@Select(" select cspi.*,(cspi.integrated_tax_fee - project_amount) as totalCharge," +
			" cstri.repay,cstri.status,csp.project_name,csp.project_desc,csp.party_id," +
			" IF(cspi.user_type=1,csa.idno,ma.idno) AS idno," +
			" IF(cspi.user_type=1,csa.acctno,ub.acctno) AS acctno," +
			" IF(cspi.user_type=1,csa.acctphone,ub.telephone) AS acctphone," +
			" IF(cspi.user_type=1,csa.bankname,ub.bankname) AS bankname, " +
			" IF(cspi.user_type=1,zc.phone,zm.phone) AS phone, " +
			" ap1.name as provincename,ac1.name as cityname,csa.address,csp.crowdsource_city, " +
			" csp.compname,c.crowdsource_type,c.contactname,c.contactphone,c.contactaddress," +
			" csp.pay_id AS payId,ub.alipay_account as alipayAccount," +
			" ap.name contactprovince,ac.name contactcity,aa.name contactarea," +
			" csp.bid_type,csp.bid_end,csp.bid_project_finish,csp.notify_upload_delivery  " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid " +
			" left join zdd_crowdsource_trade_record_item cstri on cspi.id = cstri.project_item_id and cstri.repay = 0 " +
			" left join zdd_crowdsource zc on zc.crowdsourceid = cspi.userid and cspi.user_type = 1 " +
			" left join zdd_crowdsource_account csa on csa.crowdsourceid = cspi.userid and cspi.user_type = 1 " +
			" left join zdd_maker zm on zm.userid = cspi.userid and cspi.user_type = 2 " +
			" left join zdd_maker_account ma on ma.userid = cspi.userid and cspi.user_type = 2 " +
			" left join zdd_maker_studio ms on ms.userid = cspi.userid and cspi.user_type = 2 " +
			" left join zdd_maker_bankcard ub on ub.userid = ma.userid and ub.state = 1 and ub.accttype = 1 " +
			" join zdd_company c on c.compid = csp.compid " +
			" left join zdd_address ap on ap.id = c.contactprovinceid " +
			" left join zdd_address ac on ac.id = c.contactcityid " +
			" left join zdd_address aa on aa.id = c.contactareaid " +
			" left join zdd_address ap1 on ap1.id = csa.province  " +
			" left join zdd_address ac1 on ac1.id = csa.city  " +
			" where cspi.id = #{id}")
	CrowdsourceProjectItemDetailDto getItemDetailDto(Integer id);

	@Select(" select cspi.*,(cspi.integrated_tax_fee - project_amount) as totalCharge," +
			" cstri.repay,cstri.status,csp.project_name,csp.project_desc, " +
			" IF(cspi.user_type=1,csa.idno,ma.idno) AS idno," +
			" IF(cspi.user_type=1,csa.acctno,ub.acctno) AS acctno," +
			" IF(cspi.user_type=1,csa.acctphone,ub.telephone) AS acctphone," +
			" IF(cspi.user_type=1,csa.bankname,ub.bankname) AS bankname," +
			" csp.bid_type,csp.bid_end,csp.bid_project_finish,csp.notify_upload_delivery " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid " +
			" left join zdd_crowdsource_trade_record_item cstri on cspi.id = cstri.project_item_id and cstri.repay = 0 " +
			" left join zdd_crowdsource_account csa on csa.crowdsourceid = cspi.userid and cspi.user_type = 1 " +
			" left join zdd_maker_account ma on ma.userid = cspi.userid and cspi.user_type = 2 " +
			" left join zdd_maker_bankcard ub on ub.userid = ma.userid and ub.state = 1 and ub.accttype = 1 " +
			" where cspi.userid = #{userid} " +
			" and cspi.user_type = #{userType} " +
			" and csp.state > 1" +
			" and cspi.deleted = 0 " +
			" order by csp.createtime desc ")
	List<CrowdsourceProjectItemDto> getItemListByUserid(int userid, int userType);

	@Select(" select IFNULL(SUM(cspi.project_amount), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid " +
			" where cspi.userid = #{userid} " +
			" and if(#{compid} = 0, 1=1, csp.compid = #{compid})" +
			" and cspi.user_type = #{userType} " +
			" and cspi.projectid != #{excludeProjectid}" +
			" and DATE_FORMAT(cspi.createtime,'%Y%m') = DATE_FORMAT(NOW(),'%Y%m')" +
			" and csp.state in(2,3,4,5) " +
			" and cspi.deleted = 0")
	BigDecimal monthlyTotalProjectAmount(int userid, int compid, int userType, int excludeProjectid);

	@Select(" select IFNULL(SUM(cspi.project_amount), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid " +
			" join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states}) " +
			" where cspi.userid = #{userid} " +
			" and if(#{compid} = 0, 1=1, csp.compid = #{compid})" +
			" and cspi.user_type = #{userType} " +
			" and cspi.projectid != #{excludeProjectid}" +
			" and DATE_FORMAT(cspi.createtime,'%Y%m') = DATE_FORMAT(NOW(),'%Y%m')" +
			" and csp.state in(2,3,4,5) " +
			" and cspi.deleted = 0")
	BigDecimal monthlyTotalProjectAmountByStates(int userid, int compid, int userType, int excludeProjectid, String states);

	@Select(" select IFNULL(SUM(cspi.project_service_charge), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid " +
			" where cspi.userid = #{userid} " +
			" and if(#{compid} = 0, 1=1, csp.compid = #{compid})" +
			" and cspi.user_type = #{userType} " +
			" and cspi.projectid != #{excludeProjectid}" +
			" and DATE_FORMAT(cspi.createtime,'%Y%m') = DATE_FORMAT(NOW(),'%Y%m') " +
			" and csp.state in(2,3,4,5) " +
			" and cspi.deleted = 0")
	BigDecimal monthlyTotalTaxFee(int userid, int compid, int userType, int excludeProjectid);

	@Select(" select IFNULL(SUM(cspi.project_service_charge), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid " +
			" join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states}) " +
			" where cspi.userid = #{userid} " +
			" and if(#{compid} = 0, 1=1, csp.compid = #{compid})" +
			" and cspi.user_type = #{userType} " +
			" and cspi.projectid != #{excludeProjectid}" +
			" and DATE_FORMAT(cspi.createtime,'%Y%m') = DATE_FORMAT(NOW(),'%Y%m') " +
			" and csp.state in(2,3,4,5) " +
			" and cspi.deleted = 0")
	BigDecimal monthlyTotalTaxFeeByStates(int userid, int compid, int userType, int excludeProjectid, String states);

	@Select(" select IFNULL(SUM(cspi.integrated_tax_fee), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" where IF(#{userid}=0, 1=1, cspi.userid = #{userid}) " +
			" and cspi.user_type = #{userType} " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{year}=0, YEAR(cspi.createtime) = YEAR(NOW()), YEAR(cspi.createtime) = #{year}) " +
			" and IF(#{month}=0,MONTH(cspi.createtime) = MONTH(NOW()),MONTH(cspi.createtime) = #{month} )")
	BigDecimal getMonthTotalIntegratedAmount(@Param("userid") int userid, @Param("userType") int userType,
											 @Param("projectid") int projectid, @Param("year") int year, @Param("month") int month);

	// 自然人项目金额统计
	@Select(" select IFNULL(SUM(cspi.project_amount), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" where IF(#{userid}=0, 1=1, cspi.userid = #{userid}) " +
			" and IF(#{userType} = 2, cspi.user_type = 2, cspi.user_type = 1 AND csp.crowdsource_city = #{cityId} ) " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{year}=0, YEAR(cspi.createtime) = YEAR(NOW()), YEAR(cspi.createtime) = #{year}) " +
			" and IF(#{month}=0,MONTH(cspi.createtime) = MONTH(NOW()),MONTH(cspi.createtime) = #{month} )")
	BigDecimal getMonthTotalProjectAmount(@Param("userid") int userid, @Param("userType") int userType,@Param("cityId") Integer cityId,
	                                         @Param("projectid") int projectid, @Param("year") int year, @Param("month") int month);

	@Select(" select IFNULL(SUM(cspi.integrated_tax_fee), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99)  and csp.invoice_type = 1 " +
			" where IF(#{userid}=0, 1=1, cspi.userid = #{userid}) " +
			" and IF(#{userType}=0, 1=1, cspi.user_type = #{userType}) " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{year}=0, YEAR(cspi.createtime) = YEAR(NOW()), YEAR(cspi.createtime) = #{year}) " +
			" and IF(#{month}=0,MONTH(cspi.createtime) = MONTH(NOW()),MONTH(cspi.createtime) = #{month} )")
	BigDecimal getMonthGenTotalIntegratedAmount(@Param("userid") int userid, @Param("userType") int userType,
												@Param("projectid") int projectid, @Param("year") int year, @Param("month") int month);

	@Select(" select IFNULL(SUM(cspi.integrated_tax_fee), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states}) " +
			" where IF(#{userid}=0, 1=1, cspi.userid = #{userid}) " +
			" and IF(#{userType}=0, 1=1, cspi.user_type = #{userType}) " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{year}=0, YEAR(cstri.createtime) = YEAR(NOW()), YEAR(cstri.createtime) = #{year}) " +
			" and IF(#{month}=0, MONTH(cstri.createtime) = MONTH(NOW()), MONTH(cstri.createtime) = #{month})")
	BigDecimal getMonthlyTotalIntegratedAmountByStates(@Param("userid") int userid, @Param("userType") int userType,
													   @Param("projectid") int projectid, @Param("states") String states,
													   @Param("year") int year, @Param("month") int month);

	@Select(" select IFNULL(SUM(cspi.project_amount), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states}) " +
			" where IF(#{userid}=0, 1=1, cspi.userid = #{userid}) " +
			" and IF(#{userType} = 2, cspi.user_type = 2, cspi.user_type = 1 AND csp.crowdsource_city = #{cityId} ) " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{year}=0, YEAR(cstri.createtime) = YEAR(NOW()), YEAR(cstri.createtime) = #{year}) " +
			" and IF(#{month}=0, MONTH(cstri.createtime) = MONTH(NOW()), MONTH(cstri.createtime) = #{month})")
	BigDecimal getMonthlyTotalProjectAmountByStates(@Param("userid") int userid, @Param("userType") int userType,@Param("cityId") Integer cityId,
	                                                   @Param("projectid") int projectid, @Param("states") String states,
	                                                   @Param("year") int year, @Param("month") int month);

	@Select(" select IFNULL(SUM(cspi.integrated_tax_fee), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) and csp.invoice_type = 1 " +
			" join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states}) " +
			" where IF(#{userid}=0, 1=1, cspi.userid = #{userid}) " +
			" and IF(#{userType}=0, 1=1, cspi.user_type = #{userType}) " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{year}=0, YEAR(cstri.createtime) = YEAR(NOW()), YEAR(cstri.createtime) = #{year}) " +
			" and IF(#{month}=0, MONTH(cstri.createtime) = MONTH(NOW()), MONTH(cstri.createtime) = #{month})")
	BigDecimal getMonthlyGenTotalIntegratedAmountByStates(@Param("userid") int userid, @Param("userType") int userType,
														  @Param("projectid") int projectid, @Param("states") String states,
														  @Param("year") int year, @Param("month") int month);

	@Select(" select IFNULL(SUM(cspi.project_amount), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" where IF(#{userid}=0, 1=1, cspi.userid = #{userid}) " +
			" and IF(#{userType} = 2, cspi.user_type = 2, cspi.user_type = 1 AND csp.crowdsource_city = #{cityId} ) " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{year}=0, YEAR(cspi.createtime) = YEAR(NOW()), YEAR(cspi.createtime) = #{year}) " +
			" and IF(#{quarter}=0,QUARTER(cspi.createtime) = QUARTER(NOW()),QUARTER(cspi.createtime) = #{quarter} )")
	BigDecimal getQuarterTotalProjectAmount(@Param("userid") int userid, @Param("userType") int userType, @Param("cityId") Integer cityId,
	                                         @Param("projectid") int projectid, @Param("year") int year, @Param("quarter") int quarter);

	@Select(" select IFNULL(SUM(cspi.project_amount), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states}) " +
			" where IF(#{userid}=0, 1=1, cspi.userid = #{userid}) " +
			" and IF(#{userType} = 2, cspi.user_type = 2, cspi.user_type = 1 AND csp.crowdsource_city = #{cityId} ) " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{year}=0, YEAR(cspi.createtime) = YEAR(NOW()), YEAR(cspi.createtime) = #{year}) " +
			" and IF(#{quarter}=0,QUARTER(cspi.createtime) = QUARTER(NOW()),QUARTER(cspi.createtime) = #{quarter} )")
	BigDecimal getQuarterTotalProjectAmountByStates(@Param("userid") int userid, @Param("userType") int userType,@Param("cityId") Integer cityId,
	                                 @Param("projectid") int projectid, @Param("states") String states, @Param("year") int year, @Param("quarter") int quarter);

	@Select(" select IFNULL(SUM(cspi.integrated_tax_fee), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states}) " +
			" where IF(#{userid}=0, 1=1, cspi.userid = #{userid}) " +
			" and IF(#{userType}=0, 1=1, cspi.user_type = #{userType}) " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{year}=0, YEAR(cspi.createtime) = YEAR(NOW()), YEAR(cspi.createtime) = #{year}) " +
			" and IF(#{quarter}=0,QUARTER(cspi.createtime) = QUARTER(NOW()),QUARTER(cspi.createtime) = #{quarter} )")
	BigDecimal getQuarterTotalIntegratedAmountByStates(@Param("userid") int userid, @Param("userType") int userType,
	                                 @Param("projectid") int projectid, @Param("states") String states, @Param("year") int year, @Param("quarter") int quarter);

	@Select(" select IFNULL(SUM(cspi.project_amount), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" where IF(#{userid}=0, 1=1, cspi.userid = #{userid}) " +
			" and IF(#{userType} = 2, cspi.user_type = 2, cspi.user_type = 1 AND csp.crowdsource_city = #{cityId} ) " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{year}=0, YEAR(cspi.createtime) = YEAR(NOW()), YEAR(cspi.createtime) = #{year}) ")
	BigDecimal getYearlyTotalProjectAmount(@Param("userid") int userid, @Param("userType") int userType, @Param("cityId") Integer cityId,
	                                         @Param("projectid") int projectid, @Param("year") int year);

	@Select(" select IFNULL(SUM(cspi.project_amount), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states}) " +
			" where IF(#{userid}=0, 1=1, cspi.userid = #{userid}) " +
			" and IF(#{userType} = 2, cspi.user_type = 2, cspi.user_type = 1 AND csp.crowdsource_city = #{cityId} ) " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{year}=0, YEAR(cstri.createtime) = YEAR(NOW()), YEAR(cstri.createtime) = #{year}) ")
	BigDecimal getYearlyTotalProjectAmountByStates(@Param("userid") int userid, @Param("userType") int userType,@Param("cityId") Integer cityId,
	                                      @Param("projectid") int projectid, @Param("states") String states, @Param("year") int year);

	@Select(" select IFNULL(SUM(cspi.project_amount), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" where cspi.userid = #{userid} " +
			" and cspi.user_type = #{userType} " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and cspi.createtime >= DATE_SUB(DATE_FORMAT(CURRENT_DATE(),'%y-%m-01'), INTERVAL 11 MONTH) ")
	BigDecimal getTwelveMonthTotalProjectAmount(@Param("userid") int userid, @Param("userType") int userType, int projectid);

	@Select(" select IFNULL(SUM(cspi.project_amount), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states}) " +
			" where cspi.userid = #{userid} " +
			" and cspi.user_type = #{userType} " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and cspi.createtime >= DATE_SUB(DATE_FORMAT(CURRENT_DATE(),'%y-%m-01'), INTERVAL 11 MONTH) ")
	BigDecimal getTwelveMonthTotalProjectAmountByStates(@Param("userid") int userid, @Param("userType") int userType, int projectid,
	                                             @Param("states") String states);

	@Select(" select GROUP_CONCAT(IF(cspi.user_type=1,csa.realname,ma.realname)) " +
			" from zdd_crowdsource_project_item cspi " +
			" left join zdd_crowdsource_account csa on csa.crowdsourceid = cspi.userid  and cspi.user_type = 1 " +
			" left join zdd_maker_account ma on ma.userid = cspi.userid and cspi.user_type = 2 " +
			" where cspi.projectid = #{projectid}")
	String getItemCrowdsourceNames(Integer projectid);

	/**
	 * 获取项目绑定的自然人/创客
	 * @param projectid
	 * @return
	 */
	@Select(" SELECT cpi.projectid, cpi.userid AS crowdsourceid, " +
			" if(cpi.user_type = 1,ca.natural_number,ma.individual_account_number) as naturalNumber, " +
			" if(cpi.user_type = 1,ca.idno,ma.idno) as idno, " +
			" if(cpi.user_type = 1,ca.idno,mb.acctno) as yunKongAcctno,cpi.project_amount,cpi.user_type " +
			" FROM zdd_crowdsource_project_item cpi " +
			" LEFT JOIN zdd_crowdsource_account ca ON ca.crowdsourceid = cpi.userid and cpi.user_type = 1 " +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = cpi.userid and cpi.user_type = 2 " +
			" LEFT JOIN zdd_maker_bankcard mb ON mb.userid = cpi.userid and cpi.user_type = 2 " +
			" WHERE cpi.projectid = #{projectid} AND cpi.deleted = 0 ")
	List<CrowdsourceRelevantInfoDto> queryCrowdsourceRelevantInfo(Integer projectid);


	/**
	 * 获取众智包项目人员信息
	 * @param projectid
	 * @return
	 */
	@Select(" SELECT cpi.* " +
			" FROM zdd_crowdsource_project_item cpi " +
			" WHERE cpi.projectid = #{projectid} AND cpi.deleted = 0 ")
	List<CrowdsourceProjectItem> queryCrowdsourceProjectItem(Integer projectid);

	@Select(" select cspi.*,(cspi.integrated_tax_fee - project_amount) as totalCharge," +
			" cstri.repay,cstri.status,csp.bid_type,csp.bid_end,csp.bid_project_finish,csp.notify_upload_delivery " +
			" IF(cspi.user_type=1,csa.idno,ma.idno) AS idno," +
			" IF(cspi.user_type=1,csa.acctno,ub.acctno) AS acctno," +
			" IF(cspi.user_type=1,csa.acctphone,ub.telephone) AS acctphone," +
			" IF(cspi.user_type=1,csa.bankname,ub.bankname) AS bankname," +
			" csp.bid_type,csp.bid_end,csp.bid_project_finish,csp.notify_upload_delivery " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid " +
			" left join zdd_crowdsource_trade_record_item cstri on cspi.id = cstri.project_item_id " +
			" left join zdd_crowdsource_account csa on csa.crowdsourceid = cspi.userid and cspi.user_type = 1 " +
			" left join zdd_maker_account ma on ma.userid = cspi.userid and cspi.user_type = 2 " +
			" left join zdd_maker_bankcard ub on ub.userid = ma.userid and ub.state = 1 and ub.accttype = 1 " +
			" where cspi.projectid = #{projectid} " +
			" and cspi.deleted = 0 " +
			" order by cspi.id ")
	List<CrowdsourceProjectItemBriefDto> listProjectItemBrief(int projectid);

	@Select(" SELECT cspi.user_type,cspi.userid,cspi.project_amount,cspi.integrated_tax_fee AS sumAmount, " +
			" ( cspi.integrated_tax_fee - cspi.project_amount ) AS projectServiceCharge, cp.project_name, " +
			" cspi.username AS studioName, " +
			" cspi.realname AS userName, " +
			" IF ( cspi.user_type = 1, c.phone, m.phone ) AS userPhone, " +
			" IF ( cspi.user_type = 1, '', ma.email ) AS userEmail, " +
			" cp.compname,company.regphone AS companyPhone, zca.realname AS companyLegalPerson, " +
			" company.compemail AS companyEmail,cstri.finishtime " +
			" FROM zdd_crowdsource_project_item cspi " +
			" LEFT JOIN zdd_crowdsource_trade_record_item cstri ON cspi.id = cstri.project_item_id " +
			" LEFT JOIN zdd_crowdsource_project cp ON cp.projectid = cspi.projectid " +
			" LEFT JOIN zdd_crowdsource c ON c.crowdsourceid = cspi.userid AND cspi.user_type = 1 " +
			" LEFT JOIN zdd_crowdsource_account csa ON csa.crowdsourceid = cspi.userid AND cspi.user_type = 1 " +
			" LEFT JOIN zdd_maker m ON m.userid = cspi.userid AND cspi.user_type = 2 " +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = cspi.userid AND cspi.user_type = 2 " +
			" LEFT JOIN zdd_maker_studio ms ON ms.userid = ma.userid AND cspi.user_type = 2 " +
			" LEFT JOIN zdd_company company ON company.compid = cp.compid " +
			" LEFT JOIN zdd_company_account zca ON zca.compid = cp.compid " +
			" WHERE " +
			" cspi.projectid = #{projectid} AND cspi.deleted = 0 ORDER BY cspi.id ")
	List<ProjectAttachmentDto> projectAttachmentDetail(int projectid);

	@Select(" SELECT IF(ISNULL( sum( project_amount ) ) OR sum( project_amount ) = '', 0.0, sum( project_amount )) " +
			" FROM zdd_crowdsource_project_item  cpi " +
			" left join zdd_crowdsource_trade_record_item ctri on cpi.id = ctri.project_item_id " +
			" WHERE IF(#{flag} = 1, DATE_FORMAT( cpi.createtime, '%Y%m' ) = DATE_FORMAT( CURDATE( ), '%Y%m' ), YEAR ( cpi.createtime ) = YEAR ( NOW( ) )) " +
			" AND cpi.user_type = #{userType} AND cpi.userid = #{userId} AND ctri.status in(1,2) ")
	double statisticsMoney(@Param("flag") int flag, @Param("userType") int userType, @Param("userId") int userId);

	@Select(" delete cspi,i  from zdd_crowdsource_project_item cspi " +
			" left join zdd_crowdsource_trade_record_item i  on i.project_item_id = cspi.id " +
			" where cspi.userid = #{userid} and cspi.user_type = #{userType} " +
			" and YEAR(cspi.createtime) = YEAR(NOW())")
	Integer delYearlyProjectData(int userid, int userType);

	@Select(" delete cspi,i  from zdd_crowdsource_project_item cspi " +
			" left join zdd_crowdsource_trade_record_item i  on i.project_item_id = cspi.id " +
			" where cspi.userid = #{userid} and cspi.user_type = #{userType} " +
			" and YEAR(cspi.createtime) = YEAR(NOW())" +
			" and QUARTER(cspi.createtime) = #{quarter}")
	Integer delQuarterProjectData(int userid, int userType, int quarter);

	@Select(" select IFNULL(SUM(item.integrated_tax_fee), 0) " +
			" from zdd_crowdsource_project_item item" +
			" left join zdd_crowdsource_project pro on item.projectid = pro.projectid" +
			" left join zdd_company com on pro.compid = com.compid" +
			" where com.compid in(${compids}) " +
			" and DATE(item.createtime) between DATE(#{from}) and DATE(#{to}) ")
	BigDecimal getTotalAmountByCompids(String compids, Date from, Date to);

	@Select(" select IFNULL(SUM(integrated_tax_fee), 0) " +
			" from zdd_crowdsource_project_item " +
			" where userid = #{userid} " +
			" and DATE(createtime) between DATE(#{from}) and DATE(#{to}) ")
	BigDecimal getTotalAmountByUserid(int userid, Date from, Date to);

	@Select(" select IFNULL(SUM(cspi.integrated_tax_fee), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states}) " +
			" where cspi.userid = #{userid} " +
			" and cspi.user_type = 2" +
			" and cspi.deleted = 0 " +
			" and cspi.createtime between DATE(#{from}) and DATE(#{to})")
	BigDecimal getTotalCrowdsourceAmountByUserid(int userid, Date from, Date to, String states);

	// 获取企业的项目数据 selectType=1：15分钟内交易的数据 selectType=1：根据项目ID查询
	@Select(" SELECT cspi.projectid,cspi.userid,compid,cspi.realname,cspi.username,project_amount as projectAmount,cspi.user_type " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" left join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states})" +
			" where csp.compid = #{compid} and cspi.deleted = 0 " +
			" AND if(#{selectType} = 1,csp.state in (2,3,4,5) and CURRENT_DATE() = DATE(cstri.tradetime)," +
			"  csp.projectid in (${projectIds}) )")
	List<SameProjectDto> getTimeSlotProject(Integer selectType, Integer compid, String states, String projectIds);

	@Select("SELECT case item.user_type when 2 then item.username else item.realname end as username,round(item.integrated_tax_fee,2) as integratedTaxFee," +
			" round(item.project_amount,2) as projectAmount " +
			" from zdd_crowdsource_project_item item " +
			" left join zdd_crowdsource_project pro on pro.projectid = item.projectid " +
			" left join zdd_maker mak on item.userid = mak.userid " +
			" left join zdd_maker_account acc on mak.userid = acc.userid " +
			" left join zdd_maker_studio stu on mak.userid = stu.userid " +
			" where item.projectid = #{projectid}")
	List<ExportProjectAcceptanceItemDto> getProjectAcceptanceItemList(Integer projectid);

	@Select(" select cspi.* " +
			" from zdd_crowdsource_project_item item cspi " +
			" where cspi.projectid = #{projectid} " +
			" and NOT ISNULL(cspi.delivery_path) " +
			" and cspi.delivery_path != ''")
	List<CrowdsourceProjectDeliveryFileDto> listProjectDeliveryFiles(Integer projectid);

	@Select(" SELECT COUNT(id) FROM `zdd_crowdsource_project_item` " +
			" WHERE projectid = #{projectid} " +
			" AND deleted = 0 ")
	Integer obtainTheNumberOfParticipants(@Param("projectid") Integer projectid);

	@Select(" select IFNULL(SUM(cspi.project_amount), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" where cspi.userid in (${userIds}) " +
			" and IF(#{userType} = 2, cspi.user_type = 2, cspi.user_type = 1) " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{timeType} = 1 ,YEAR(cspi.createtime) = YEAR(NOW()), " +
			"     cspi.createtime >= DATE_SUB(DATE_FORMAT(CURRENT_DATE(),'%y-%m-01'), INTERVAL 11 MONTH))")
	BigDecimal getCroOddAllAmountByUserIds(@Param("userIds") String userIds, @Param("userType") int userType,
										  @Param("projectid") int projectid, @Param("timeType") int timeType);

	@Select(" select IFNULL(SUM(cspi.project_amount), 0) " +
			" from zdd_crowdsource_project_item cspi " +
			" join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99) " +
			" join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (${states}) " +
			" where cspi.userid in (${userIds}) " +
			" and IF(#{userType} = 2, cspi.user_type = 2, cspi.user_type = 1) " +
			" and cspi.projectid != #{projectid} " +
			" and cspi.deleted = 0 " +
			" and IF(#{timeType} = 1 ,YEAR(cspi.createtime) = YEAR(NOW()), " +
			"     cspi.createtime >= DATE_SUB(DATE_FORMAT(CURRENT_DATE(),'%y-%m-01'), INTERVAL 11 MONTH))")
	BigDecimal getCroOddAmountByUserIds(@Param("userIds") String userIds, @Param("userType") int userType,
										@Param("projectid") int projectid,@Param("states") String states, @Param("timeType") int timeType);

}
